package com.example.ending_demo.dao;

import com.example.ending_demo.model.Address;
import com.example.ending_demo.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

public class AddressDao {

    //新增通讯录
    public int addAddress(Address address) throws SQLException {
        Connection conn = DBUtils.getConnection();

        String sql = "insert into address(user_id,addr_username,addr_phone,addr_email,address) values(?,?,?,?,?)";
        PreparedStatement preStmt  = conn.prepareStatement(sql);
        preStmt.setInt(1,address.getUser_id());
        preStmt.setString(2,address.getAddr_username());
        preStmt.setString(3,address.getAddr_phone());
        preStmt.setString(4,address.getAddr_email());
        preStmt.setString(5,address.getAddress());

        int result = preStmt.executeUpdate();
        DBUtils.release(preStmt,conn);
        return result;

    }


    //通过User_id获得地址列表
    public List<Address> getAddressByUserID(int user_id){
        ArrayList<Address> addrList = new ArrayList<>();

        Connection conn = DBUtils.getConnection();
        String sql = "select * from address where user_id = ?";
        try {
            PreparedStatement pStat = conn.prepareStatement(sql);
            pStat.setInt(1,user_id);
            ResultSet rs = pStat.executeQuery();

            while (rs.next()){
                Address addrObj = new Address();
                addrObj.setUser_id(user_id);
                addrObj.setAddr_email(rs.getString("addr_email"));
                addrObj.setAddress_id(rs.getInt("address_id"));
                addrObj.setAddr_phone(rs.getString("addr_phone"));
                addrObj.setAddress(rs.getString("address"));
                addrObj.setAddr_username(rs.getString("addr_username"));
                addrList.add(addrObj);
            }
            DBUtils.release(rs,pStat,conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return addrList;
    }

    //根据通讯录的ID查询通讯录信息
    public Address getAddressByAddressID(int addr_id) throws SQLException {
        Address addrObj = null;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from address where address_id = ?";
        PreparedStatement pstat = conn.prepareStatement(sql);
        pstat.setInt(1,addr_id);
        ResultSet rs = pstat.executeQuery();

        while (rs.next()){
            addrObj = new Address();
            addrObj.setUser_id(rs.getInt("user_id"));
            addrObj.setAddress_id(rs.getInt("address_id"));
            addrObj.setAddr_username(rs.getString("addr_username"));
            addrObj.setAddr_phone(rs.getString("addr_phone"));
            addrObj.setAddr_email(rs.getString("addr_email"));
            addrObj.setAddress(rs.getString("address"));

        }
        return addrObj;

    }

    //根系用户地址信息
    public int updateAddress(Address addrObj) throws SQLException {
        Connection conn = DBUtils.getConnection();
        String sql = "update address" + " set addr_username = ?, addr_phone = ?, addr_email = ?, address = ?" +
                " where address_id = ?";
        PreparedStatement pstat = conn.prepareStatement(sql);
        pstat.setString(1,addrObj.getAddr_username());
        pstat.setString(2,addrObj.getAddr_phone());
        pstat.setString(3,addrObj.getAddr_email());
        pstat.setString(4,addrObj.getAddress());
        pstat.setInt(5,addrObj.getAddress_id());

        int result = pstat.executeUpdate();

        return result;

    }

    //查询通讯录所有数量
    public Integer queryForPageTotalCount(Integer user_id){

        String sql = "select count(*) from address where user_id = ?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement pstat = conn.prepareStatement(sql);
            pstat.setInt(1,user_id);
            ResultSet rs = pstat.executeQuery();
            if(rs.next()){
                Integer temp = rs.getInt(1);
                DBUtils.release(rs,pstat,conn);
                return temp;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return null;
    }

    //查询每一页的数据
    public List<Address> queryForPageItems(Integer begin,Integer pageSize,int user_id){
        ArrayList<Address> addrList = new ArrayList<>();

        Connection conn = DBUtils.getConnection();
        String sql = "select * from address where user_id = ? limit ?,?";
        try {
            PreparedStatement pStat = conn.prepareStatement(sql);
            pStat.setInt(1,user_id);
            pStat.setInt(2,begin);
            pStat.setInt(3,pageSize);
            ResultSet rs = pStat.executeQuery();

            while (rs.next()){
                Address addrObj = new Address();
                addrObj.setUser_id(user_id);
                addrObj.setAddr_email(rs.getString("addr_email"));
                addrObj.setAddress_id(rs.getInt("address_id"));
                addrObj.setAddr_phone(rs.getString("addr_phone"));
                addrObj.setAddress(rs.getString("address"));
                addrObj.setAddr_username(rs.getString("addr_username"));
                addrList.add(addrObj);
            }
            DBUtils.release(rs,pStat,conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return addrList;
    }

    //搜索查询去重
    public List<Address> searchAddress(String searchStr,int user_id){
        ArrayList<Address> tempList = new ArrayList<>();
        Connection conn = DBUtils.getConnection();
        //查询username中包括关键字
        String uSql = "select * from address where addr_username like ? AND user_id = ?";
        try {
            PreparedStatement upstat = conn.prepareStatement(uSql);
            upstat.setString(1,"%"+searchStr+"%");
            upstat.setInt(2,user_id);
            ResultSet urs = upstat.executeQuery();
            while (urs.next()){
                Address uaddress = new Address();
                uaddress.setUser_id(user_id);
                uaddress.setAddr_email(urs.getString("addr_email"));
                uaddress.setAddress_id(urs.getInt("address_id"));
                uaddress.setAddr_phone(urs.getString("addr_phone"));
                uaddress.setAddress(urs.getString("address"));
                uaddress.setAddr_username(urs.getString("addr_username"));
                tempList.add(uaddress);
            }
            urs.close();
            upstat.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        //查询phone中包括关键字
        String pSql = "select * from address where addr_phone like ? AND user_id = ?";
        try {
            PreparedStatement ppstat = conn.prepareStatement(pSql);
            ppstat.setString(1,"%"+searchStr+"%");
            ppstat.setInt(2,user_id);
            ResultSet prs = ppstat.executeQuery();
            while (prs.next()){
                Address paddress = new Address();
                paddress.setUser_id(user_id);
                paddress.setAddr_email(prs.getString("addr_email"));
                paddress.setAddress_id(prs.getInt("address_id"));
                paddress.setAddr_phone(prs.getString("addr_phone"));
                paddress.setAddress(prs.getString("address"));
                paddress.setAddr_username(prs.getString("addr_username"));
                tempList.add(paddress);
            }
            prs.close();
            ppstat.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // 查询邮箱中包含关键字
        String eSql = "select * from address where addr_email like ? AND user_id = ?";
        try {
            PreparedStatement epstat = conn.prepareStatement(eSql);
            epstat.setString(1,"%"+searchStr+"%");
            epstat.setInt(2,user_id);
            ResultSet ers = epstat.executeQuery();
            while (ers.next()){
                Address eaddress = new Address();
                eaddress.setUser_id(user_id);
                eaddress.setAddr_email(ers.getString("addr_email"));
                eaddress.setAddress_id(ers.getInt("address_id"));
                eaddress.setAddr_phone(ers.getString("addr_phone"));
                eaddress.setAddress(ers.getString("address"));
                eaddress.setAddr_username(ers.getString("addr_username"));
                tempList.add(eaddress);
            }
            ers.close();
            epstat.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // 查询地址中含有关键字的
        String aSql = "select * from address where address like ? AND user_id = ?";
        try {
            PreparedStatement apstat = conn.prepareStatement(aSql);
            apstat.setString(1,"%"+searchStr+"%");
            apstat.setInt(2,user_id);
            ResultSet ars = apstat.executeQuery();
            while (ars.next()){
                Address aaddress = new Address();
                aaddress.setUser_id(user_id);
                aaddress.setAddr_email(ars.getString("addr_email"));
                aaddress.setAddress_id(ars.getInt("address_id"));
                aaddress.setAddr_phone(ars.getString("addr_phone"));
                aaddress.setAddress(ars.getString("address"));
                aaddress.setAddr_username(ars.getString("addr_username"));
                tempList.add(aaddress);
            }
            ars.close();
            apstat.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        // 使用LinkedHashSet进行去重并保持顺序
        Set<Address> set = new LinkedHashSet<>(tempList);
        // 将去重后的元素放回ArrayList中
        List<Address> addrList = new ArrayList<>(set);

        for (Address a:addrList){
            System.out.println(a);
        }

        return addrList;

    }


    //删除地址
    public int deleteAddress(Integer address_id){
        Connection conn = DBUtils.getConnection();
        String sql = "delete from address where address_id = ?";
        try {
            PreparedStatement pstat = conn.prepareStatement(sql);
            pstat.setInt(1,address_id);
            return pstat.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
